if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetProtocolsListForPerson]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetProtocolsListForPerson]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

--[GetProtocolsListForPerson] 'CTA','Active'

CREATE PROCEDURE [dbo].[GetProtocolsListForPerson]
(
	@userName VARCHAR(12),
	@status   VARCHAR (25) = null
)
 AS
/**************************************************************************
* Name:    GetProtocolsListForPerson
*
* Purpose: Provide IRB data for Web service
* Notes:	to be called from Web Service IRBInfo
* Output: List of protocols associated with the person along with his roles, filtered by the protocol status if status is passed
*--------------------------------------------------------------------------
* Date          Initials    Modification
*--------------------------------------------------------------------------
*
* 10/21/2009    	SP          Created 	
* 11/09/2009		SP			Bug Resolved.
***************************************************************************/

SELECT DISTINCT P.ProtocolNumber AS folderNumber, SUBSTRING(ST.[Name], 1, 45) AS personRole, P.Title As studyDescription, AD.[Name] As sponsorName, PO.[Name] As status

FROM Protocol P 
	INNER JOIN dbo.ProtocolOverallStatusType PO  WITH(NOLOCK)
		ON P.OverallStatusId = PO.Id		 
	INNER JOIN dbo.ProtocolStaff_Map PS  WITH(NOLOCK)
		ON P.ProtocolNumber = PS.ProtocolNumber
	INNER JOIN dbo.AdmPerson AP  WITH(NOLOCK)
		ON AP.Id = PS.AdmPersonId
	INNER JOIN dbo.FwkDomainUser DU  WITH(NOLOCK)
		ON AP.FwkDomainUserId = DU.Id
	INNER JOIN dbo.ProtocolStaffingType ST  WITH(NOLOCK)
		ON PS.ProtocolStaffTypeId = ST.Id 
	INNER JOIN dbo.AdmSponsor AD  WITH(NOLOCK)
		ON P.AdmSponsorId = AD.Id

WHERE P.IsCurrent = '1'	
	AND DU.Username = @userName
	AND (@status IS NULL OR PO.[Name] LIKE '' + @status + '%' )
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


